﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Security;
using System.Data.OleDb;


namespace WindowsFormsApplication1
{
     public class DAL
    {
        private static readonly string u8_constr = ConfigurationManager.ConnectionStrings["u8db"].ConnectionString;
        private static readonly string user_constr = ConfigurationManager.ConnectionStrings["localuserdb"].ConnectionString;
        private static readonly string bi_constr = ConfigurationManager.ConnectionStrings["bidb"].ConnectionString;
     //   private static readonly string _connStr = "Data Source=10.10.120.252;Initial Catalog=ourway_hengyang_dw;User Id=sa;Password=jbdg@cs123";
        public DataSet ExtractData(string ssql,SqlParameter [] pms)
        {
            //获取U8中的数据
            SqlConnection conn = new SqlConnection(u8_constr);
            conn.Open();
           /* string ssql = GetSQL(selectdate);
            DataSet ds = new DataSet();
            ds = GetDataSet(ds,ssql,conn,"tb");
            ssql = GetLJSQL(selectdate);
            ds = GetDataSet(ds,ssql,conn,"tb_lj");*/
            SqlDataAdapter dapt = new SqlDataAdapter(ssql, conn);
            if (pms != null)
            {
                dapt.SelectCommand.Parameters.AddRange(pms);
                dapt.SelectCommand.CommandType = CommandType.StoredProcedure;
                dapt.SelectCommand.CommandTimeout = 0;
            }
            
            DataSet ds = new DataSet();
            dapt.Fill(ds,"f_tlweeklyreport");
            conn.Close();
            //获取BI中的数据
            conn = new SqlConnection(bi_constr);
            conn.Open();
            ssql = "select 日期,区域,isnull(round(年度发货预算额,2),0) 年度发货预算额,isnull(round(年度回款预算额,2),0) 年度回款预算额,isnull(round(期初,2),0) 期初 from tl销售周报年度值";
            dapt = new SqlDataAdapter(ssql,conn);
            dapt.Fill(ds,"f_yearplan");
            return ds;
        }

        public string CheckLogin(string worknum, string pwd)
        {
            pwd = MD5String(pwd);
            SqlConnection conn = new SqlConnection(user_constr);
            conn.Open();
            string ssql = "select  userrealname from t_user where username = '"+worknum+"' and userpassword = '"+pwd+"'";
            SqlCommand cmd = new SqlCommand(ssql,conn);
            string name=cmd.ExecuteScalar().ToString();
            conn.Close();
            return name;
        }

        private static string MD5String(string str)
        {
            return System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(str, "md5");
        }

        public bool ImportDataToBI(DataTable dt,string endtime,string week) 
        {
            bool result = false;
            SqlConnection conn = new SqlConnection(bi_constr);
            conn.Open();
            SqlTransaction tran = conn.BeginTransaction();
            try
            {
                string ssql = "select  count(1) from f_tlweeklyreport where year(fdate)=year('" + endtime + "') and month(fdate)=month('" + endtime + "') and fweek='"+week+"'";
                SqlCommand cmd = new SqlCommand(ssql, conn);
                cmd.Transaction = tran;
                string num = cmd.ExecuteScalar().ToString();
                if (null == num || int.Parse(num) <= 0)
                {
                    result = InsertData(dt,conn,tran,week);
                }
                else
                {
                    if (!DeleteData(conn,endtime,tran,week))
                    {
                        return false;
                    }
                    result = InsertData(dt,conn,tran,week);
                }
                if (result)
                {
                    tran.Commit();
                    ssql.Clone();
                }
                return result;
            }
            catch (Exception e) 
            {
                tran.Rollback();
                return false;
            }
        }

        private bool DeleteData(SqlConnection conn,string endtime,SqlTransaction tran,string week) 
        {
            try
            {
                string ssql = "delete  from f_tlweeklyreport where year(fdate)=year('" + endtime + "') and month(fdate)=month('" + endtime + "') and fweek='"+week+"'";
                SqlCommand cmd = new SqlCommand(ssql, conn);
                cmd.Transaction = tran;
                int num = cmd.ExecuteNonQuery();
                if (num > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception e) 
            {
                throw(e);
            }
        }

        private bool InsertData(DataTable dt,SqlConnection conn,SqlTransaction tran,string week) 
        {
            try
            {
                int count = 0;
                bool result = false;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string ssql = "insert  into f_tlweeklyreport(fweek,fbr,fdate,farea,fmonthout,fmonthin,fout_lj,fin_lj,fbegin,fyearplanout,fyearplanin,ffinishoutrate,ffinishinrate,fid,fye) values('"+week+"','04','" + dt.Rows[i]["fdate"].ToString() + "','" + dt.Rows[i]["farea"].ToString() + "'," +
                    dt.Rows[i]["fmonthout"].ToString() + "," + dt.Rows[i]["fmonthin"].ToString() + "," + dt.Rows[i]["fout_lj"].ToString() + "," + dt.Rows[i]["fin_lj"].ToString() + "," + dt.Rows[i]["fbegin"].ToString() + "," + dt.Rows[i]["fyearplanout"].ToString() + "," +
                    dt.Rows[i]["fyearplanin"].ToString() + "," + dt.Rows[i]["ffinishoutrate"].ToString() + "," + dt.Rows[i]["ffinishinrate"].ToString() + "," + dt.Rows[i]["fid"].ToString() + "," + dt.Rows[i]["fye"].ToString() + ")";
                    SqlCommand cmd = new SqlCommand(ssql, conn);
                    cmd.Transaction = tran;
                    count = count + cmd.ExecuteNonQuery();

                }
                if (count > 0)
                {
                    result = InsertWD(conn, tran);
                    return result;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception e)
            {
                throw(e);
            }
        }

        private bool InsertWD(SqlConnection conn, SqlTransaction tran) 
        {
            try
            {
                string ssql = "truncate  table  d_tlweeklyreport_area;insert into d_tlweeklyreport_area select  distinct isnull(farea,'空白') farea from  f_tlweeklyreport";
                SqlCommand cmd = new SqlCommand(ssql, conn);
                cmd.Transaction = tran;
                int num = cmd.ExecuteNonQuery();
                if (num > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception e)
            { 
                throw(e);
            }
        }

    /*   public DataSet ExtractExcel(string Path)
        {
          //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
            string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + Path + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            DataSet ds = new DataSet();
            string ssql = "select * from [年度值$]";
            OleDbDataAdapter dapt = new OleDbDataAdapter(ssql, strConn);
            dapt.Fill(ds,"Excel");
            conn.Close();
            return ds;
        }

       public void WriteExcel(DataSet ds)
       {
           DataTable dt = ds.Tables["Excel"];
           SqlConnection conn = new SqlConnection(bi_constr);
           conn.Open();
           //string ssql="Insert  into  特缆销售周报年度值(fdate,farea,fbegin,fyearplanout,fyearplanin) values"
           conn.Close();

       }
         */
       /* private DataSet GetDataSet(DataSet ds,string ssql,SqlConnection conn,string tablename) 
        {
            SqlDataAdapter adpt = new SqlDataAdapter(ssql, conn);
            adpt.Fill(ds,tablename);
            return ds;

        }

        private string GetSQL(string selectdate) 
        {
            string sql_select = "select  max(c.cccname) as farea,cast(sum(jf)/10000 as decimal(18,2)) as fmonthout,cast(sum(df)/10000 as decimal(18,2)) as fmonthin from (Select max(dregdate) as fdate,max(cdwccode) as cdwccode,max(customerclass.cccname) as cdwcname,max(cdwcode) cdwcode,max(cdwname) as cdwname,max(cdwabbname) as cdwabbname,SUM(case when  ((cProcStyle not in (N'26',N'27',N'28',N'29',N'EX26',N'EX27') or (iperiod=0 and dispatchlists.idlsid is null)) and (cprocstyle<>N'XJ' or ccovouchtype in (N'60',N'SR45'))) and a.cexch_name<>N'人民币' then iDAmount_f-iCAmount_f else 0 end) as qc_f,SUM(case when  ((cProcStyle not in (N'26',N'27',N'28',N'29',N'EX26',N'EX27') or (iperiod=0 and dispatchlists.idlsid is null)) and (cprocstyle<>N'XJ' or ccovouchtype in (N'60',N'SR45'))) and ccovouchtype=cprocstyle then iDAmount_s-iCAmount_s else 0 end) as qc_s,SUM(case when  ((cProcStyle not in (N'26',N'27',N'28',N'29',N'EX26',N'EX27') or (iperiod=0 and dispatchlists.idlsid is null)) and (cprocstyle<>N'XJ' or ccovouchtype in (N'60',N'SR45'))) then iDAmount-iCAmount else 0 end) as qc,SUM(case when  (cProcStyle not in (N'26',N'27',N'28',N'29',N'EX26',N'EX27') and (cprocstyle<>N'XJ' or ccovouchtype in (N'60',N'SR45'))) and a.cexch_name<>N'人民币' then iDAmount_f else 0 end) as jf_f,SUM(case when  (cProcStyle not in (N'26',N'27',N'28',N'29',N'EX26',N'EX27') and (cprocstyle<>N'XJ' or ccovouchtype in (N'60',N'SR45'))) and ccovouchtype=cprocstyle then iDAmount_s else 0 end) as jf_s,SUM(case when  (cProcStyle not in (N'26',N'27',N'28',N'29',N'EX26',N'EX27') and (cprocstyle<>N'XJ' or ccovouchtype in (N'60',N'SR45'))) then iDAmount else 0 end) as jf,SUM(case when  (cProcStyle not in (N'26',N'27',N'28',N'29',N'EX26',N'EX27') and (cprocstyle<>N'XJ' or ccovouchtype in (N'60',N'SR45'))) and a.cexch_name<>N'人民币' then iCAmount_f else 0 end) as df_f,SUM(case when  (cProcStyle not in (N'26',N'27',N'28',N'29',N'EX26',N'EX27') and (cprocstyle<>N'XJ' or ccovouchtype in (N'60',N'SR45'))) and ccovouchtype=cprocstyle then iCAmount_s else 0 end) as df_s,SUM(case when  (cProcStyle not in (N'26',N'27',N'28',N'29',N'EX26',N'EX27') and (cprocstyle<>N'XJ' or ccovouchtype in (N'60',N'SR45'))) then iCAmount else 0 end) as df,N'AR' as csysid From Ar_DetailCust_s  a with (nolock) left join dispatchlists on a.idlsid=dispatchlists.idlsid  LEFT JOIN CustomerClass with (nolock) ON a.cdwccode=CustomerClass.cCCCode where  ((cProcStyle not in (N'26',N'27',N'28',N'29',N'EX26',N'EX27') or (iperiod=0 and dispatchlists.idlsid is null)) and (cprocstyle<>N'XJ' or ccovouchtype in (N'60',N'SR45')))  and a.iflag<=2  Group by cdwccode,cdwcode,dregdate union all Select max(dvouchdate) as fdate,max(cdwccode) as cdwccode,max(customerclass.cccname) as cdwcname,max(cdwcode) as cdwcode,max(cdwname) as cdwname,max(cdwabbname) as cdwabbname,SUM(case when  a.cexch_name<>N'人民币' then iAmount_f else 0 end) as qc_f,SUM(iAmount_s) as qc_s,SUM(iAmount) as qc,SUM(case when  a.cexch_name<>N'人民币' then iAmount_f else 0 end) as jf_f,SUM(iAmount_s) as jf_s,SUM(iAmount) as jf,0 as df_f,0 as df_s,0 as df,N'AR' as csysid From V_OutNotBalance_A a with (nolock) LEFT JOIN CustomerClass with (nolock) ON a.cdwccode=CustomerClass.cCCCode where  cCheckMan<>'' Group By cdwccode,cdwcode,dvouchdate) s join  customerclass c on left(s.cdwccode,2)=c.ccccode where convert(varchar(8),fdate,120) like '%" + selectdate + "%'" +
         " group by  year(fdate),month(fdate),left(cdwccode,2)";
         return sql_select;
        }

        private string GetLJSQL(string selectdate) 
        {
            string sql_lj_select = "select  max(c.cccname) as farea,cast(sum(jf)/10000 as decimal(18,2)) as fout_lj,cast(sum(df)/10000 as decimal(18,2)) as fin_lj from (Select max(dregdate) as fdate,max(cdwccode) as cdwccode,max(customerclass.cccname) as cdwcname,max(cdwcode) cdwcode,max(cdwname) as cdwname,max(cdwabbname) as cdwabbname,SUM(case when  ((cProcStyle not in (N'26',N'27',N'28',N'29',N'EX26',N'EX27') or (iperiod=0 and dispatchlists.idlsid is null)) and (cprocstyle<>N'XJ' or ccovouchtype in (N'60',N'SR45'))) and a.cexch_name<>N'人民币' then iDAmount_f-iCAmount_f else 0 end) as qc_f,SUM(case when  ((cProcStyle not in (N'26',N'27',N'28',N'29',N'EX26',N'EX27') or (iperiod=0 and dispatchlists.idlsid is null)) and (cprocstyle<>N'XJ' or ccovouchtype in (N'60',N'SR45'))) and ccovouchtype=cprocstyle then iDAmount_s-iCAmount_s else 0 end) as qc_s,SUM(case when  ((cProcStyle not in (N'26',N'27',N'28',N'29',N'EX26',N'EX27') or (iperiod=0 and dispatchlists.idlsid is null)) and (cprocstyle<>N'XJ' or ccovouchtype in (N'60',N'SR45'))) then iDAmount-iCAmount else 0 end) as qc,SUM(case when  (cProcStyle not in (N'26',N'27',N'28',N'29',N'EX26',N'EX27') and (cprocstyle<>N'XJ' or ccovouchtype in (N'60',N'SR45'))) and a.cexch_name<>N'人民币' then iDAmount_f else 0 end) as jf_f,SUM(case when  (cProcStyle not in (N'26',N'27',N'28',N'29',N'EX26',N'EX27') and (cprocstyle<>N'XJ' or ccovouchtype in (N'60',N'SR45'))) and ccovouchtype=cprocstyle then iDAmount_s else 0 end) as jf_s,SUM(case when  (cProcStyle not in (N'26',N'27',N'28',N'29',N'EX26',N'EX27') and (cprocstyle<>N'XJ' or ccovouchtype in (N'60',N'SR45'))) then iDAmount else 0 end) as jf,SUM(case when  (cProcStyle not in (N'26',N'27',N'28',N'29',N'EX26',N'EX27') and (cprocstyle<>N'XJ' or ccovouchtype in (N'60',N'SR45'))) and a.cexch_name<>N'人民币' then iCAmount_f else 0 end) as df_f,SUM(case when  (cProcStyle not in (N'26',N'27',N'28',N'29',N'EX26',N'EX27') and (cprocstyle<>N'XJ' or ccovouchtype in (N'60',N'SR45'))) and ccovouchtype=cprocstyle then iCAmount_s else 0 end) as df_s,SUM(case when  (cProcStyle not in (N'26',N'27',N'28',N'29',N'EX26',N'EX27') and (cprocstyle<>N'XJ' or ccovouchtype in (N'60',N'SR45'))) then iCAmount else 0 end) as df,N'AR' as csysid From Ar_DetailCust_s  a with (nolock) left join dispatchlists on a.idlsid=dispatchlists.idlsid  LEFT JOIN CustomerClass with (nolock) ON a.cdwccode=CustomerClass.cCCCode where  ((cProcStyle not in (N'26',N'27',N'28',N'29',N'EX26',N'EX27') or (iperiod=0 and dispatchlists.idlsid is null)) and (cprocstyle<>N'XJ' or ccovouchtype in (N'60',N'SR45')))  and a.iflag<=2  Group by cdwccode,cdwcode,dregdate union all Select max(dvouchdate) as fdate,max(cdwccode) as cdwccode,max(customerclass.cccname) as cdwcname,max(cdwcode) as cdwcode,max(cdwname) as cdwname,max(cdwabbname) as cdwabbname,SUM(case when  a.cexch_name<>N'人民币' then iAmount_f else 0 end) as qc_f,SUM(iAmount_s) as qc_s,SUM(iAmount) as qc,SUM(case when  a.cexch_name<>N'人民币' then iAmount_f else 0 end) as jf_f,SUM(iAmount_s) as jf_s,SUM(iAmount) as jf,0 as df_f,0 as df_s,0 as df,N'AR' as csysid From V_OutNotBalance_A a with (nolock) LEFT JOIN CustomerClass with (nolock) ON a.cdwccode=CustomerClass.cCCCode where  cCheckMan<>'' Group By cdwccode,cdwcode,dvouchdate) s join  customerclass c on left(s.cdwccode,2)=c.ccccode where Year(fdate)=left('"+selectdate+"',4) and month(fdate)>0 and month(fdate)<=right('"+selectdate+"',2)" +
         " group by left(cdwccode,2)";
            return sql_lj_select;
        }*/
    }
}
